package com.spring.app.util;

import java.awt.Color;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;
import java.util.concurrent.Future;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.annotation.AsyncResult;
import org.springframework.stereotype.Component;

import com.spring.app.domain.BuyerData;
import com.spring.app.genpostagelabel.BuyerDetail;
import com.spring.app.genpostagelabel.ColumnIndexes;
import com.spring.app.genpostagelabel.ExcelConstants;
import com.spring.app.genpostagelabel.LabelInfo;
import com.spring.app.genpostagelabel.Utils;

@Component
public class ExcelFileUtil implements ExcelConstants {
	@Autowired
	CsvFileUtil csvFileUtil;

	public Map readExcel(String fileName) throws Exception {

		FileInputStream file = new FileInputStream(new File("C:\\ebay",
				fileName));

		// Get the workbook instance for XLS file
		HSSFWorkbook workbook = new HSSFWorkbook(file);

		// Get first sheet from the workbook
		HSSFSheet sheet = workbook.getSheetAt(0);

		// Get iterator to all the rows in current sheet
		Iterator<Row> rowIterator = sheet.iterator();
		List<BuyerData> buyerDetails = new ArrayList<BuyerData>();
		Map<String, BuyerData> buyerDetailsMap = new HashMap<String, BuyerData>();
		List<String> duplicatesBuyerIds = new ArrayList<String>();
		int rc = 1;
		while (rowIterator.hasNext()) {
			Row row = rowIterator.next();

			if (row.getLastCellNum() >= 32
					&& !(Utils.isEmpty(getStringValue(row
							.getCell(ColumnIndexes.COL_NO_USER_ID))) || getStringValue(
							row.getCell(ColumnIndexes.COL_NO_USER_ID)).equals(
							"User Id"))) {

				BuyerData buyerDetail = new BuyerData();
				rc++;

				if (!Utils.isEmpty(row
						.getCell(ColumnIndexes.COL_NO_SALESRECORD)))
					buyerDetail.setSalesRecord(getStringValue(row
							.getCell(ColumnIndexes.COL_NO_SALESRECORD)));

				if (!Utils.isEmpty(row.getCell(ColumnIndexes.COL_NO_USER_ID)))
					buyerDetail.setUserId(getStringValue(row
							.getCell(ColumnIndexes.COL_NO_USER_ID)));

				if (!Utils
						.isEmpty(buyerDetailsMap.get(buyerDetail.getUserId()))) {
					duplicatesBuyerIds.add(buyerDetail.getUserId());
					buyerDetail.setFullName(buyerDetailsMap.get(
							buyerDetail.getUserId()).getFullName());
					buyerDetail.setPhoneNo(buyerDetailsMap.get(
							buyerDetail.getUserId()).getPhoneNo());
					buyerDetail.setEmail(buyerDetailsMap.get(
							buyerDetail.getUserId()).getEmail());
					buyerDetail.setAddress1(buyerDetailsMap.get(
							buyerDetail.getUserId()).getAddress1());
					buyerDetail.setAddress2(buyerDetailsMap.get(
							buyerDetail.getUserId()).getAddress2());
					buyerDetail.setCity(buyerDetailsMap.get(
							buyerDetail.getUserId()).getCity());
					buyerDetail.setCountry(buyerDetailsMap.get(
							buyerDetail.getUserId()).getCountry());
					buyerDetail.setState(buyerDetailsMap.get(
							buyerDetail.getUserId()).getState());
					buyerDetail.setPostcode(buyerDetailsMap.get(
							buyerDetail.getUserId()).getPostcode());

				} else {
					if (!Utils.isEmpty(row
							.getCell(ColumnIndexes.COL_NO_FULLNAME)))
						buyerDetail.setFullName(getStringValue(row
								.getCell(ColumnIndexes.COL_NO_FULLNAME)));
					if (!Utils.isEmpty(row
							.getCell(ColumnIndexes.COL_NO_PHONE_NO)))
						buyerDetail.setPhoneNo(getStringValue(row
								.getCell(ColumnIndexes.COL_NO_PHONE_NO)));
					if (!Utils.isEmpty(row.getCell(ColumnIndexes.COL_NO_EMAIL)))
						buyerDetail.setEmail(getStringValue(row
								.getCell(ColumnIndexes.COL_NO_EMAIL)));
					if (!Utils.isEmpty(row
							.getCell(ColumnIndexes.COL_NO_ADDRESS_1)))
						buyerDetail.setAddress1(getStringValue(row
								.getCell(ColumnIndexes.COL_NO_ADDRESS_1)));
					if (!Utils.isEmpty(row
							.getCell(ColumnIndexes.COL_NO_ADDRESS_2)))
						buyerDetail.setAddress2(getStringValue(row
								.getCell(ColumnIndexes.COL_NO_ADDRESS_2)));
					if (!Utils.isEmpty(row.getCell(ColumnIndexes.COL_NO_CITY)))
						buyerDetail.setCity(getStringValue(row
								.getCell(ColumnIndexes.COL_NO_CITY)));
					if (!Utils.isEmpty(row.getCell(ColumnIndexes.COL_NO_STATE)))
						buyerDetail.setState(getStringValue(row
								.getCell(ColumnIndexes.COL_NO_STATE)));
					if (!Utils.isEmpty(row
							.getCell(ColumnIndexes.COL_NO_POSTCODE)))
						buyerDetail.setPostcode(getStringValue(row
								.getCell(ColumnIndexes.COL_NO_POSTCODE)));
					if (!Utils.isEmpty(row
							.getCell(ColumnIndexes.COL_NO_COUNTRY)))
						buyerDetail.setCountry(getStringValue(row
								.getCell(ColumnIndexes.COL_NO_COUNTRY)));
				}
				// if(!Utils.isEmpty(row.getCell(ColumnIndexes.COL_NO_ITEM_ID)))
				// buyerDetail.setItemId(getStringValue(row.getCell(ColumnIndexes.COL_NO_ITEM_ID)));
				/*
				 * buyerDetail.setTransactionId(row.getCell(ColumnIndexes.
				 * COL_NO_TRANSACTION_ID).getStringCellValue());
				 * buyerDetail.setItemTitle
				 * (row.getCell(ColumnIndexes.COL_NO_ITEM_TITLE
				 * ).getStringCellValue());
				 * buyerDetail.setQuantity(row.getCell(ColumnIndexes
				 * .COL_NO_QUANTITY).getStringCellValue());
				 * buyerDetail.setSalePrice
				 * (row.getCell(ColumnIndexes.COL_NO_SALE_PRICE
				 * ).getStringCellValue());
				 * buyerDetail.setPostage(row.getCell(ColumnIndexes
				 * .COL_NO_POSTAGE).getStringCellValue());
				 * buyerDetail.setTotalPrice
				 * (row.getCell(ColumnIndexes.COL_NO_TOTAL_PRICE
				 * ).getStringCellValue());
				 * buyerDetail.setSaleDate(row.getCell(ColumnIndexes
				 * .COL_NO_SALE_DATE).getStringCellValue());
				 * buyerDetail.setCheckOutDate
				 * (row.getCell(ColumnIndexes.COL_NO_CHECKOUT_DATE
				 * ).getStringCellValue());
				 * buyerDetail.setPaidDate(row.getCell(ColumnIndexes
				 * .COL_NO_PAID_ON_DATE).getStringCellValue());
				 */

				buyerDetailsMap.put(buyerDetail.getUserId(), buyerDetail);
				buyerDetails.add(buyerDetail);

			}

		}

		Set<BuyerData> buyerDataSet = new TreeSet<BuyerData>(
				new BuyerDataComparator());
		buyerDataSet.addAll(buyerDetails);

		buyerDetails.retainAll(buyerDataSet);

		Collections.sort(buyerDetails, new BuyerDataComparator());
		Map m = new HashMap();
		m.put("buyerDataList", buyerDetails);
		m.put("repeatedBuyers", duplicatesBuyerIds);

		return m;
	}

	@Async
	public void generatePostageLabels(Map model) {
		try {
			List<BuyerData> buyerDataList = (List) model.get("buyerDataList");
			List<String> duplicates = (List) model.get("repeatedBuyers");
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet sheet = workbook.createSheet("Sample sheet");
			int rownum = 0;
			int count = 0;

			HSSFPrintSetup printSetup = sheet.getPrintSetup();
			printSetup.setHeaderMargin(HEADERMARGIN_VAL);
			printSetup.setPaperSize(PAPERSIZE);
			printSetup.setFooterMargin(FOOTERMARGIN_VAL);

			// set the format for sheet
			sheet.setMargin(HSSFSheet.BottomMargin, BOTTOMMARGIN_VAL);
            sheet.setMargin(HSSFSheet.TopMargin, TOPMARGIN_VAL);
            sheet.setMargin(HSSFSheet.LeftMargin, LEFTMARGIN_VAL);
            sheet.setMargin(HSSFSheet.RightMargin, RIGHTMARGIN_VAL);

            sheet.setColumnWidth(0, CELL_ITEM_WIDTH);
            sheet.setColumnWidth(1, CELL_ADDR_WIDTH);
            sheet.setColumnWidth(2, CELL_ITEM_WIDTH);
            sheet.setColumnWidth(3, CELL_ADDR_WIDTH);
            sheet.setDefaultRowHeight((short) CELL_HEIGHT);

			
			for (BuyerData buyerData : buyerDataList) {
				Row row = null;
				Cell cell0;
				Cell cell;
				if (Utils.isEmpty(sheet.getRow(rownum)))
					row = sheet.createRow(rownum);
					if(row != null)
						row.setHeightInPoints(CELL_HEIGHT_IN_POINTS);	
				if (count % 2 == 1) {
					row = sheet.getRow(rownum);
					row.setHeightInPoints(CELL_HEIGHT_IN_POINTS);
					cell0 = row.createCell(2);
					cell = row.createCell(3);
					rownum++;
				} else {
					row = sheet.getRow(rownum);
					row.setHeightInPoints(CELL_HEIGHT_IN_POINTS);
					cell0 = row.createCell(0);
					cell = row.createCell(1);
				}
				count++;

				/****************/
				
				
				
				HSSFCellStyle cellAddrStyle = workbook.createCellStyle();
				HSSFCellStyle cellItemStyle = workbook.createCellStyle();

				HSSFFont cellFont = workbook.createFont();
				HSSFFont cellItemFont = workbook.createFont();

				// Bold character
				// cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
				// Aerial font
				cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
				cellFont.setFontName(this.ITEMLIST_CELL_FONT_NAME);
				cellItemFont.setFontName(this.CELL_FONT_NAME);
				cellItemFont.setStrikeout(true);
				// Font Size 12
				cellFont.setFontHeightInPoints((short) this.ITEMLIST_CELL_FONT_SIZE);
				cellItemFont
						.setFontHeightInPoints((short) this.CELL_ITEMCODE_SIZE);
				// cellItemFont.setColor(HSSFColor.WHITE.index);

				// set cell font
				cellAddrStyle.setFont(cellFont);
				cellItemStyle.setFont(cellItemFont);
				// set cell alignment
				cellAddrStyle
						.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION);
				cellAddrStyle
						.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
				cellItemStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
				cellItemStyle
						.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
				cellItemStyle.setRotation(CELL_ITEMCODE_ROTATION);
				// cell wrap text
				cellAddrStyle.setWrapText(true);
				cellItemStyle.setWrapText(true);
				// set cell width & height
				/****************/
				CellStyle verticalWrap = workbook.createCellStyle();
				verticalWrap.setRotation((short) 90);
				// cell0.setCellStyle(verticalWrap);
				cell0.setCellStyle(cellItemStyle);
				cell0.setCellValue("ITEM#1");

				CellStyle multiLineWrap = workbook.createCellStyle();
				multiLineWrap.setWrapText(true);
				HSSFFont hSSFFont = workbook.createFont();
				hSSFFont.setFontName(HSSFFont.FONT_ARIAL);
				hSSFFont.setFontHeightInPoints((short) 15);
				multiLineWrap.setFont(hSSFFont);
				multiLineWrap.setIndention((short) 100);
				if (duplicates != null
						&& duplicates.contains(buyerData.getUserId())) {
					multiLineWrap
							.setFillForegroundColor(IndexedColors.GREY_25_PERCENT
									.getIndex());
					multiLineWrap.setFillPattern(CellStyle.SOLID_FOREGROUND);

					cellAddrStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
					cellAddrStyle
							.setFillForegroundColor(IndexedColors.GREY_25_PERCENT
									.getIndex());
				}

				// cell.setCellStyle(multiLineWrap);
				cell.setCellStyle(cellAddrStyle);

				String cellData = "";
				if (!Utils.isEmpty(buyerData.getFullName()))
					cellData += buyerData.getFullName();

				if (!Utils.isEmpty(buyerData.getAddress1()))
					cellData += "\n" + buyerData.getAddress1();

				if (!Utils.isEmpty(buyerData.getAddress2()))
					cellData += "\n" + buyerData.getAddress2();

				if (!Utils.isEmpty(buyerData.getCity()))
					cellData += "\n" + buyerData.getCity();

				if (!Utils.isEmpty(buyerData.getCountry()))
					cellData += "\n" + buyerData.getCountry();

				if (!Utils.isEmpty(buyerData.getPostcode()))
					cellData += " - " + buyerData.getPostcode();

				HSSFRichTextString richTextLabel = new HSSFRichTextString(
						cellData);
				System.out.println("Labels in Rich Text format = "
						+ richTextLabel);
				cell.setCellValue(richTextLabel);
				// cell.setCellValue(cellData);

			}

			String fileName = model
					.get("fileName")
					.toString()
					.substring(0,
							model.get("fileName").toString().lastIndexOf("."));

			if (model.get("fileName").toString().endsWith("xls"))
				fileName += "_xls";

			FileOutputStream out = new FileOutputStream(new File("C:\\ebay\\"
					+ fileName + "_postageLabel" + ".xls"));
			workbook.write(out);
			out.close();
			System.out.println("Excel written successfully..");

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private boolean checkIfExists(List<BuyerData> buyerDataList,
			BuyerData buyerData) {

		for (BuyerData bd : buyerDataList) {
			if (bd.getUserId().equals(buyerData.getUserId()))
				return true;
		}
		// TODO Auto-generated method stub
		return false;
	}

	public String getStringValue(Cell cell) {
		String value = "";
		try {

			if (cell != null) {
				int type = cell.getCellType();
				if (type == HSSFCell.CELL_TYPE_STRING) {
					value = cell.getRichStringCellValue().toString();
				} else if (type == HSSFCell.CELL_TYPE_NUMERIC) {
					double val = cell.getNumericCellValue();
					long valLong = (long) Math.floor(val);
					value = Long.toString(valLong);
				} else if (type == HSSFCell.CELL_TYPE_BOOLEAN) {
					boolean val = cell.getBooleanCellValue();
					value = Boolean.toString(val);
				} else if (type == HSSFCell.CELL_TYPE_BLANK) {
					value = "";
				}
			} else {
				System.out.println("Error!! cell is null");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return value;
	}

	public static void main(String[] args) {
		Map model = new HashMap();
		model.put("fileName", "test.xls");
		ExcelFileUtil u = new ExcelFileUtil();
		List<BuyerData> b = new ArrayList<BuyerData>();
		BuyerData buyerData = new BuyerData();

		buyerData.setFullName("test1");
		buyerData.setAddress1("Address 1");
		buyerData.setAddress2("Address 2");
		buyerData.setCity("Sydney");
		buyerData.setCountry("Australia");
		buyerData.setPostcode("2000");
		b.add(buyerData);
		buyerData.setFullName("test2");
		buyerData.setAddress1("Address 1-2");
		buyerData.setAddress2("Address 2-2");
		buyerData.setCity("Melbourne");
		buyerData.setCountry("Australia");
		buyerData.setPostcode("3000");
		b.add(buyerData);

		buyerData.setFullName("test3");
		buyerData.setAddress1("Address 1-3");
		buyerData.setAddress2("Address 2-3");
		buyerData.setCity("Rhodes");
		buyerData.setCountry("Australia");
		buyerData.setPostcode("2138");
		b.add(buyerData);

		model.put("buyerDataList", b);

		u.generatePostageLabels(model);
	}

}

class BuyerDataComparator implements Comparator<BuyerData> {

	@Override
	public int compare(BuyerData bd1, BuyerData bd2) {

		return bd1.getSalesRecord().compareTo(bd2.getSalesRecord());
	}

}
